cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Figure3.log, replace

***Prepare the time-variant VC-backed dummy variable
use ./6_investors.dta, clear
*keep only VC investors
keep if investortype==5
sort orgnr time
**identify the first VC investment in each firm
bys orgnr: keep if _n==1
*784 first VC investments
keep orgnr time
gen firstvc=1
merge 1:1 orgnr time using ./5_postmoney_valuations_failures.dta
drop if _merge==1
drop _merge
sort orgnr time
order orgnr time firstvc
bys orgnr: replace firstvc=firstvc[_n-1] if firstvc==. & firstvc[_n-1]!=.
replace firstvc=0 if firstvc==.
drop vcbacked
rename firstvc vcbacked
save ./helpfile_vcdynamic.dta, replace

***Prepare the time-variant multi-round dummy variable
use ./5_postmoney_valuations_failures.dta, clear
sort orgnr time
**identify the actual financing round (disregard the fictive zero/failed rounds)
keep if mv_rounds!=. & mv_rounds!=0
sort orgnr time
**identify each round
bys orgnr: gen round=_n
keep orgnr time round
merge 1:1 orgnr time using ./helpfile_vcdynamic.dta
drop _merge
sort orgnr time
bys orgnr: replace round=round[_n-1] if round==. & round[_n-1]!=.
replace round=0 if round==.
gen multiround=(round>1)
order orgnr time vcbacked multiround round

****generate firm age variable
merge m:1 orgnr using ./1n_founding_dates.dta
keep if _merge==3
drop _merge
***some firms in 2010 do not have the specific registration/founding date, we drop those observations
drop if ftime==.
gen firmage=time-ftime+1
order orgnr time vcbacked multiround round firmage

**aggregate valuation observations before the founding date
replace firmage=0 if firmage<0
**aggregate valuation observations older than 10 years
replace firmage=120 if firmage>120

**keep only HIP firms - this is our baseline
keep if sample==1
save ./dynamic_analyses.dta, replace

******************************************************************
******************************************************************

***Figure 3-- compare different subsamples of firms; based on valuations in financing rounds; with winsorization 99.5%
use ./dynamic_analyses.dta, clear
**vc-backed dummy senior to multiround; categories mutually exclusive
replace multiround=0 if vcbacked==1
gen vcage=vcbacked*firmage
gen multiage=multiround*firmage

replace mv_rounds=mv_rounds/1000000
sum mv_rounds, d
return list
sum mv_rounds if mv_rounds>r(p99), d
/*
*distribution of the top 1% of valuations
                          mv_rounds
-------------------------------------------------------------
      Percentiles      Smallest
 1%     390.4108       387.5084
 5%     402.0843        387.552
10%     422.0802        388.751       Obs                 460
25%     497.2175       389.0288       Sum of wgt.         460

50%      691.014                      Mean           1979.791
                        Largest       Std. dev.      4835.477
75%     1396.318        37079.8
90%     3428.183       40929.16       Variance       2.34e+07
95%     6571.463       42104.82       Skewness       6.331451
99%     34812.58       46331.19       Kurtosis       48.59049
*/
return list
*winsorize top 0.5%
replace mv_rounds=r(p50) if mv_rounds>r(p50) & mv_rounds!=.
*230 changes

**for the table output
reg mv_rounds i.firmage i.vcbacked i.vcage i.multiround i.multiage 
outreg2 using ./figures/Figure3.xls, bdec(3) label ctitle("Fig3") replace keep (i.firmage i.vcbacked i.vcage i.multiround i.multiage) 

*for the figure output
reg mv_rounds i.firmage i.vcbacked i.vcage i.multiround i.multiage 
gen vcpremium=_b[1.vcbacked] if firmage==0
gen multipremium=_b[1.multiround] if firmage==0
gen single=0 if firmage==0

forvalues i = 1(1)120 {
capture {
replace vcpremium = _b[`i'.vcage] if firmage==`i'
replace multipremium= _b[`i'.multiage] if firmage==`i'
replace single= _b[`i'.firmage] if firmage==`i'
}
}

collapse (min) single vcpremium multipremium, by(firmage)

foreach i in vcpremium multipremium {
gen `i'1=`i' if firmage==0
egen `i'2=min(`i'1)
replace `i'=`i'+`i'2 if firmage>0
}
keep firmage single vcpremium multipremium
replace multipremium=. if firmage==0 | firmage==1

twoway (line vcpremium firmage) (line multipremium firmage), ytitle(Valuation Premium in Mio NOK) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10") ylabel(-50(50)250) yline(0) legend(order(1 "VC valuation premium" 2 "Multiround valuation premium") position(6) region(lcolor(white))) 
graph export ./figures/Figure3.png, replace

egen avgmulti=mean(multipremium) 
egen avgvc=mean(vcpremium)
gen bias=avgvc/avgmulti
*avgmulti	avgvc	bias
*29.83755	73.86945	2.475721

*********************************************************************************
**FOR REFEREE** 
*********************************************************************************
*********************************************************************************
***Figure 3-- compare different subsamples of firms; based on valuations in financing rounds; unwinsorized; for the referee
use ./dynamic_analyses.dta, clear
**vc-backed dummy senior to multiround; categories mutually exclusive
replace multiround=0 if vcbacked==1
gen vcage=vcbacked*firmage
gen multiage=multiround*firmage

replace mv_rounds=mv_rounds/1000000

*for the figure output
reg mv_rounds i.firmage i.vcbacked i.vcage i.multiround i.multiage 
gen vcpremium=_b[1.vcbacked] if firmage==0
gen multipremium=_b[1.multiround] if firmage==0
gen single=0 if firmage==0

forvalues i = 1(1)120 {
capture {
replace vcpremium = _b[`i'.vcage] if firmage==`i'
replace multipremium= _b[`i'.multiage] if firmage==`i'
replace single= _b[`i'.firmage] if firmage==`i'
}
}

collapse (min) single vcpremium multipremium, by(firmage)

foreach i in vcpremium multipremium {
gen `i'1=`i' if firmage==0
egen `i'2=min(`i'1)
replace `i'=`i'+`i'2 if firmage>0
}
keep firmage single vcpremium multipremium
replace multipremium=. if firmage==0 | firmage==1

twoway (line vcpremium firmage) (line multipremium firmage), ytitle(Valuation Premium in Mio NOK) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10")  yline(0) legend(order(1 "VC valuation premium" 2 "Multiround valuation premium" ) position(6) region(lcolor(white))) 
graph export ./figures/Figure3_nowins.png, replace

egen avgmulti=mean(multipremium) 
egen avgvc=mean(vcpremium)
gen bias=avgvc/avgmulti
*avgmulti	avgvc	     bias
*61.58063	78.70527	1.278085

***********************************************************************
***********************************************************************
***Figure 3-- compare different subsamples of firms; based on valuations in financing rounds; with winsorization 1-99% -- for the referee
use ./dynamic_analyses.dta, clear
**vc-backed dummy senior to multiround; categories mutually exclusive
replace multiround=0 if vcbacked==1
gen vcage=vcbacked*firmage
gen multiage=multiround*firmage

replace mv_rounds=mv_rounds/1000000
winsor2 mv_rounds, replace 

*for the figure output
reg mv_rounds i.firmage i.vcbacked i.vcage i.multiround i.multiage 
gen vcpremium=_b[1.vcbacked] if firmage==0
gen multipremium=_b[1.multiround] if firmage==0
gen single=0 if firmage==0

forvalues i = 1(1)120 {
capture {
replace vcpremium = _b[`i'.vcage] if firmage==`i'
replace multipremium= _b[`i'.multiage] if firmage==`i'
replace single= _b[`i'.firmage] if firmage==`i'
}
}

collapse (min) single vcpremium multipremium, by(firmage)

foreach i in vcpremium multipremium {
gen `i'1=`i' if firmage==0
egen `i'2=min(`i'1)
replace `i'=`i'+`i'2 if firmage>0
}
keep firmage single vcpremium multipremium
replace multipremium=. if firmage==0 | firmage==1

twoway (line vcpremium firmage) (line multipremium firmage), ytitle(Valuation Premium in Mio NOK) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10")  yline(0) legend(order(1 "VC valuation premium" 2 "Multiround valuation premium" ) position(6) region(lcolor(white))) 
graph export ./figures/Figure3_99win.png, replace

egen avgmulti=mean(multipremium) 
egen avgvc=mean(vcpremium)
gen bias=avgvc/avgmulti
*avgmulti	avgvc	bias
*25.45712	63.85471	2.508324


******************************************************************
******************************************************************

***Figure 3-- compare different subsamples of firms; based on *all* valuations; with winsorization 99.5%
use ./dynamic_analyses.dta, clear
**vc-backed dummy senior to multiround; categories mutually exclusive
replace multiround=0 if vcbacked==1
gen vcage=vcbacked*firmage
gen multiage=multiround*firmage

replace mv_all=mv_all/1000000
sum mv_all, d
return list
sum mv_all if mv_all>r(p99), d
/*
*distribution of the top 1% of valuations
                           mv_all
-------------------------------------------------------------
      Percentiles      Smallest
 1%      387.552       384.0126
 5%     400.6355       384.0968
10%     424.1604       384.4886       Obs                 654
25%     503.6962       384.7155       Sum of wgt.         654

50%     749.4704                      Mean           2485.341
                        Largest       Std. dev.      17584.26
75%      1649.82       38534.83
90%      2740.05       42104.82       Variance       3.09e+08
95%     5713.372       46331.19       Skewness       23.49106
99%     27141.81         438750       Kurtosis       581.2839
*/
return list
*winsorize top 0.5%
replace mv_all=r(p50) if mv_all>r(p50) & mv_all!=.
*327 changes

**for the table output
reg mv_all i.firmage i.vcbacked i.vcage i.multiround i.multiage 
outreg2 using ./figures/Figure3.xls, bdec(3) label ctitle("FigB2") append keep (i.firmage i.vcbacked i.vcage i.multiround i.multiage) 

*for the figure output
reg mv_all i.firmage i.vcbacked i.vcage i.multiround i.multiage 
gen vcpremium=_b[1.vcbacked] if firmage==0
gen multipremium=_b[1.multiround] if firmage==0
gen single=0 if firmage==0

forvalues i = 1(1)120 {
capture {
replace vcpremium = _b[`i'.vcage] if firmage==`i'
replace multipremium= _b[`i'.multiage] if firmage==`i'
replace single= _b[`i'.firmage] if firmage==`i'
}
}

collapse (min) single vcpremium multipremium, by(firmage)

foreach i in vcpremium multipremium {
gen `i'1=`i' if firmage==0
egen `i'2=min(`i'1)
replace `i'=`i'+`i'2 if firmage>0
}
keep firmage single vcpremium multipremium
replace multipremium=. if firmage==0 | firmage==1

twoway (line vcpremium firmage) (line multipremium firmage), ytitle(Valuation Premium in Mio NOK) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10")  yline(0) legend(order(1 "VC valuation premium" 2 "Multiround valuation premium") position(6) region(lcolor(white))) 
graph export ./figures/FigureB1.png, replace

egen avgmulti=mean(multipremium) 
egen avgvc=mean(vcpremium)
gen bias=avgvc/avgmulti
*avgmulti	avgvc	bias
*31.27033	77.0424	2.463754



